package cn.iocoder.yudao.module.vos.dal.mysql.ecdr;

import cn.iocoder.yudao.module.vos.controller.admin.ecdr.vo.ECdrPageReqVO;

import java.time.*;
import java.time.format.DateTimeFormatter;
import java.util.*;

/** 必须 public */
public class ECdrSqlProvider {

    // 菲律宾时区
    private static final ZoneId ZONE = ZoneId.of("Asia/Manila");
    private static final DateTimeFormatter YMD = DateTimeFormatter.ofPattern("yyyyMMdd");

    /** 分表前缀固定为 e_cdr（得到的表如 e_cdr_20250822） */
    private static String baseTable() {
        return "e_cdr";
    }

    /** starttime 为空 → 当天一张表；否则按区间逐日拼表（均按马尼拉日期） */
    private static List<String> resolveTables(ECdrPageReqVO req) {
        String base = baseTable();
        List<String> tbs = new ArrayList<String>();

        Long[] st = req.getStarttime();
        boolean empty = st == null || st.length == 0
                || (st.length == 1 && st[0] == null)
                || (st.length >= 2 && st[0] == null && st[1] == null);

        if (empty) {
            LocalDate today = LocalDate.now(ZONE);
            tbs.add(base + "_" + today.format(YMD));
            return tbs;
        }

        long from = (st.length >= 1 && st[0] != null) ? st[0] : st[1];
        long to   = (st.length >= 2 && st[1] != null) ? st[1] : st[0];
        if (to < from) {
            long tmp = from; from = to; to = tmp;
        }

        LocalDate d1 = Instant.ofEpochMilli(from).atZone(ZONE).toLocalDate();
        LocalDate d2 = Instant.ofEpochMilli(to).atZone(ZONE).toLocalDate();

        for (LocalDate d = d1; !d.isAfter(d2); d = d.plusDays(1)) {
            tbs.add(base + "_" + d.format(YMD));
        }
        return tbs;
    }

    private static void appendWhere(StringBuilder sb) {
        sb.append(" WHERE 1=1 ");
        sb.append("<if test=\"req.callere164 != null and req.callere164 != ''\"> AND callere164 = #{req.callere164} </if>");
        sb.append("<if test=\"req.calleee164 != null and req.calleee164 != ''\"> AND calleee164 = #{req.calleee164} </if>");
        sb.append("<if test=\"req.calleeaccesse164 != null and req.calleeaccesse164 != ''\"> AND calleeaccesse164 = #{req.calleeaccesse164} </if>");
        sb.append("<if test=\"req.callerip != null and req.callerip != ''\"> AND callerip = #{req.callerip} </if>");
        sb.append("<if test=\"req.callergatewayid != null and req.callergatewayid != ''\"> AND callergatewayid = #{req.callergatewayid} </if>");
        sb.append("<if test=\"req.calleeip != null and req.calleeip != ''\"> AND calleeip = #{req.calleeip} </if>");
        sb.append("<if test=\"req.calleetogatewaye164 != null and req.calleetogatewaye164 != ''\"> AND calleetogatewaye164 = #{req.calleetogatewaye164} </if>");

        // starttime
        sb.append("<if test=\"req.starttime != null and req.starttime.length &gt;= 2 and req.starttime[0] != null and req.starttime[1] != null\">");
        sb.append(" AND starttime BETWEEN #{req.starttime[0]} AND #{req.starttime[1]} </if>");
        sb.append("<if test=\"req.starttime != null and req.starttime.length &gt;= 1 and req.starttime[0] != null and !(req.starttime.length &gt;= 2 and req.starttime[1] != null)\">");
        sb.append(" AND starttime &gt;= #{req.starttime[0]} </if>");
        sb.append("<if test=\"req.starttime != null and req.starttime.length &gt;= 2 and req.starttime[1] != null and (req.starttime[0] == null)\">");
        sb.append(" AND starttime &lt;= #{req.starttime[1]} </if>");

        // stoptime
        sb.append("<if test=\"req.stoptime != null and req.stoptime.length &gt;= 2 and req.stoptime[0] != null and req.stoptime[1] != null\">");
        sb.append(" AND stoptime BETWEEN #{req.stoptime[0]} AND #{req.stoptime[1]} </if>");
        sb.append("<if test=\"req.stoptime != null and req.stoptime.length &gt;= 1 and req.stoptime[0] != null and !(req.stoptime.length &gt;= 2 and req.stoptime[1] != null)\">");
        sb.append(" AND stoptime &gt;= #{req.stoptime[0]} </if>");
        sb.append("<if test=\"req.stoptime != null and req.stoptime.length &gt;= 2 and req.stoptime[1] != null and (req.stoptime[0] == null)\">");
        sb.append(" AND stoptime &lt;= #{req.stoptime[1]} </if>");

        sb.append("<if test=\"req.holdtime != null\"> AND holdtime &gt; #{req.holdtime} </if>");
        sb.append("<if test=\"req.enddirection != null\"> AND enddirection = #{req.enddirection} </if>");
        sb.append("<if test=\"req.phoneTag != null and req.phoneTag != ''\"> AND phone_tag = #{req.phoneTag} </if>");
    }


    /** ✅ 新增：无分页全量查询脚本（导出使用；无 LIMIT） */
    public static String buildSelectAllScript(Map<String, Object> params) {
        ECdrPageReqVO req = (ECdrPageReqVO) params.get("req");
        List<String> tbs = resolveTables(req);
        if (tbs.isEmpty()) {
            LocalDate today = LocalDate.now(ZONE);
            tbs.add(baseTable() + "_" + today.format(YMD));
        }

        StringBuilder sb = new StringBuilder(4096);
        sb.append("<script>");
        sb.append("SELECT * FROM (");
        for (int i = 0; i < tbs.size(); i++) {
            if (i > 0) {
                sb.append(" UNION ALL ");
            }
            sb.append("SELECT * FROM ").append(tbs.get(i));
            appendWhere(sb);
        }
        sb.append(") AS u ORDER BY u.flowno DESC ");
        sb.append("</script>");
        return sb.toString();
    }

    /** public static，且所有 if/for 都带 {} */
    public static String buildSelectPageScript(Map<String, Object> params) {
        ECdrPageReqVO req = (ECdrPageReqVO) params.get("req");
        List<String> tbs = resolveTables(req);
        if (tbs.isEmpty()) {
            LocalDate today = LocalDate.now(ZONE);
            tbs.add(baseTable() + "_" + today.format(YMD));
        }

        StringBuilder sb = new StringBuilder(4096);
        sb.append("<script>");
        sb.append("SELECT * FROM (");
        for (int i = 0; i < tbs.size(); i++) {
            if (i > 0) {
                sb.append(" UNION ALL ");
            }
            sb.append("SELECT * FROM ").append(tbs.get(i));
            appendWhere(sb);
        }
        sb.append(") AS u ORDER BY u.flowno DESC ");
        sb.append(" LIMIT #{offset}, #{limit}");
        sb.append("</script>");
        return sb.toString();
    }

    /** public static，且所有 if/for 都带 {} */
    public static String buildCountScript(Map<String, Object> params) {
        ECdrPageReqVO req = (ECdrPageReqVO) params.get("req");
        List<String> tbs = resolveTables(req);
        if (tbs.isEmpty()) {
            LocalDate today = LocalDate.now(ZONE);
            tbs.add(baseTable() + "_" + today.format(YMD));
        }

        StringBuilder sb = new StringBuilder(2048);
        sb.append("<script>");
        sb.append("SELECT SUM(cnt) FROM (");
        for (int i = 0; i < tbs.size(); i++) {
            if (i > 0) {
                sb.append(" UNION ALL ");
            }
            sb.append("SELECT COUNT(1) AS cnt FROM ").append(tbs.get(i));
            appendWhere(sb);
        }
        sb.append(") AS c");
        sb.append("</script>");
        return sb.toString();
    }
}